Electronic databases have become an indispensable part of modern-day life. Business organizations, government entities, and individuals rely heavily on all kinds of databases for information and/or services. To a business organization, its databases can be one of its most important assets and/or tools to support smooth operations.
As repositories of organized data, databases usually have to keep their content updated. Changes of database content are typically applied by executing one or more scripts of database programming statements such as those written in Structured Query Language (SQL). The execution of database changes in a particular computing environment is referred to as database deployment. Moving a database from one environment to the next is referred to as “promotion.” A high-level illustration of a typical process of database promotions is shown in FIG. 1A.
Prior approaches to database deployment have been primarily disorganized, manual processes which are quite inefficient and error-prone. For example, with the prior methods, there is no formal process for checking and testing SQL statements before proceeding to Quality Assurance (QA). Developers are often making ad-hoc changes directly to the live database, which can muddle the database with unnecessary changes and errors. As proposed SQL changes are forwarded via an ITSM (IT service management) procedure to QA, changes are usually rejected upon discovery of the first error and sent back to the developer. Developers typically have no way of tracing the source of errors, and they receive piecemeal notification after every error occurs instead of receiving one notice of all captured errors. If the database becomes filled with errors, there is no effective process to roll it back to a previous state. FIG. 1B highlights some of the deficiencies and drawbacks of such a prior art process for database deployment.
In contrast to the relatively mature process established for source code builds and deployments, there has been no systematic methodology to enforce version control over database changes in a way where the change history can be researched. As a result, it is an extremely difficult task to take the UAT environment, for example, and figure out what SQL needs to be created to apply against the Production (PROD) database to update it to the latest version. This is in stark contrast to code deployment, where old code could be simply deleted and replaced with new code.
This is further complicated by the fact that often changes have been made to the production database either as part of completing the last deployment and/or as maintenance activities. These changes are often unknown to the development team and could cause the deployment to fail. There is currently no systematic method to determine if ad-hoc changes have been applied to a PROD environment but never documented or recorded after so-called “emergency” fixes. As a result, the database becomes essentially the “system of record” as opposed to the subject of a properly implemented version control mechanism, and there is no way to track the history of changes including who made a change or when.
With the prior database deployment approaches, gathering the correct sets of SQLs for changes to production is also difficult and prone to errors, as it may be months since the last PROD promotion, and SQL changes need to be run in groups matching the changes that were done in lower environments. Also, there is no satisfactory way to pre-validate SQL changes to PROD or UAT environment in regular business hours to help guarantee that the outage event goes smoothly as far as database changes are concerned. The common practice for database testing was to use a “golden database” that is maintained by hand to run tests against, which could be highly inefficient.
Nor is there any post-deployment testing mechanism to determine with good confidence whether failures when running updated functional test are truly test failures and not because the database is out of date or not in sync.
With all these technical problems described above, there is now a particular need for improved solutions of database deployment.
There are commercial industry solutions that attempt to solve some of the above-described issues, for example, by comparing two database versions and creating change SQL based on the differences. Some organizations have adapted their development practice to use this model. Most large organizations, however, would not be able to adapt their database development practice to this paradigm. A technical solution for database changes is needed that is more adaptable for the paradigm or practice most commonly in use at large business organizations.